'******************************
'   No delivery resources　エリア＆QAM番号、自動入力
'******************************

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rg As Range
    
    Dim right6  'TSIDの6桁を抽出用
    Dim left4   'あたまの4桁抽出用
    Dim right2  'QAM番号取得用
    Dim left2   'エリア判別用
    
    Dim Aria    '関東、関西、九州　判別用
    
    
    
    For Each rg In Target
       If rg.Column = 10 Then       'J列を確認対象とする
       
       right6 = Right(rg.Value, 6)
       left4 = Left(right6, 4)
       right2 = Right(left4, 2)
       left2 = Left(left4, 2)
       
       Aria = Left(rg.Value, 3)
       
       If Aria = "[関東" Then       '関東の場合のみ
                          
           Select Case left2
           Case " 1"
           rg.Offset(0, 3).Value = "練馬"
           rg.Offset(0, 4).Value = right2
            
           Case " 2"
           rg.Offset(0, 3).Value = "杉並"
           rg.Offset(0, 4).Value = right2
           
           Case " 3"
           rg.Offset(0, 3).Value = "小金井"
           rg.Offset(0, 4).Value = right2
           
           Case " 4"
           rg.Offset(0, 3).Value = "西東京"
           rg.Offset(0, 4).Value = right2
           
           Case " 5"
           rg.Offset(0, 3).Value = "板橋"
           rg.Offset(0, 4).Value = right2
           
           Case " 6"
           rg.Offset(0, 3).Value = "調布"
           rg.Offset(0, 4).Value = right2
           
           Case " 7"
           rg.Offset(0, 3).Value = "浦和"
           rg.Offset(0, 4).Value = right2
           
           Case " 8"
           rg.Offset(0, 3).Value = "大宮"
           rg.Offset(0, 4).Value = right2
           
           Case " 9"
           rg.Offset(0, 3).Value = "高崎"
           rg.Offset(0, 4).Value = right2
                      
           Case "11"
           rg.Offset(0, 3).Value = "大和"
           rg.Offset(0, 4).Value = right2
           
           Case "12"
           rg.Offset(0, 3).Value = "相模原"
           rg.Offset(0, 4).Value = right2
                      
           Case "13"
           rg.Offset(0, 3).Value = "湘南"
           rg.Offset(0, 4).Value = right2
                      
           Case "14"
           rg.Offset(0, 3).Value = "横須賀"
           rg.Offset(0, 4).Value = right2
           
           Case "15"
           rg.Offset(0, 3).Value = "東関東"
           rg.Offset(0, 4).Value = right2
           
           Case "16"
           rg.Offset(0, 3).Value = "木更津"
           rg.Offset(0, 4).Value = right2
           
           Case "17"
           rg.Offset(0, 3).Value = "土浦"
           rg.Offset(0, 4).Value = right2
           
           Case "18"
           rg.Offset(0, 3).Value = "せたまち（黒川）"
           rg.Offset(0, 4).Value = right2
           
           Case "19"
           rg.Offset(0, 3).Value = "せたまち（新調布）"
           rg.Offset(0, 4).Value = right2
           
           Case "20"
           rg.Offset(0, 3).Value = "すみだ"
           rg.Offset(0, 4).Value = right2
           
           Case "21"
           rg.Offset(0, 3).Value = "東上"
           rg.Offset(0, 4).Value = right2
           Case "22"
           rg.Offset(0, 3).Value = "所沢"
           rg.Offset(0, 4).Value = right2
           
           Case "23"
           rg.Offset(0, 3).Value = "江戸川"
           rg.Offset(0, 4).Value = right2
           
           Case "24"
           rg.Offset(0, 3).Value = "江戸川"
           rg.Offset(0, 4).Value = right2
           
           Case "25"
           rg.Offset(0, 3).Value = "かながわｾﾝﾄﾗﾙ"
           rg.Offset(0, 4).Value = right2
           
           Case "26"
           rg.Offset(0, 3).Value = "横浜"
           rg.Offset(0, 4).Value = right2
           
           Case "27"
           rg.Offset(0, 3).Value = "仙台"
           rg.Offset(0, 4).Value = right2
           
           Case "28"
           rg.Offset(0, 3).Value = "秦野"
           rg.Offset(0, 4).Value = right2
           
           Case "41"
           rg.Offset(0, 3).Value = "札幌"
           rg.Offset(0, 4).Value = right2
               
           Case "51"
           rg.Offset(0, 3).Value = "用QAM"
           rg.Offset(0, 4).Value = right2
           rg.Offset(0, 0).Interior.ColorIndex = 3
           rg.Offset(0, 3).Interior.ColorIndex = 3
           rg.Offset(0, 4).Interior.ColorIndex = 3
           
                     
           End Select
           
           End If
           
           
        End If
        Next
 End Sub